﻿using System;
using System.Collections.Generic;
using System.Linq;
using DefaultConnection;
using MementoWeb.Models;
using PetaPoco;
using MementoLibrary.DTOs;
using AutoMapper;

namespace MementoWeb.Data
{
    public class DataRepository: IDataRepository
    {
        private Database _db = new Database("DefaultConnection");
        
        #region IDataRepository Members

        #region Categories
        public void CreateNewCategory(Category category)
        {
            _db.Save(category);
        }
        
        public void DeleteCategory(int id)
        {
            _db.Delete<Category>("WHERE categoryId=@0", id);
        }

        public Category GetCategoryById(int id)
        {
            return _db.SingleOrDefault<Category>("WHERE categoryId=@0", id);
        }

        public IEnumerable<Category> GetAllCategories()
        {
            return _db.Query<Category>("SELECT * FROM category").ToList();
        }

        public IEnumerable<Category> GetCategoriesByCategoryGroupId(int id)
        {
            return _db.Query<Category>("WHERE categorygroupid=@0", id);
        }

        public void SaveCategoryChanges(Category category)
        {
            _db.Save(category);
        }
        #endregion

        #region CategoryGroups
        public void CreateNewCategoryGroup(CategoryGroup categoryGroup)
        {
            _db.Save(categoryGroup);
        }

        public void DeleteCategoryGroup(int id)
        {
            _db.Delete<CategoryGroup>("WHERE categorygroupId=@0", id);
        }

        public CategoryGroup GetCategoryGroupById(int id)
        {
            return _db.SingleOrDefault<CategoryGroup>("WHERE categorygroupId=@0", id);
        }

        public IEnumerable<CategoryGroup> GetAllCategoryGroups()
        {
            return _db.Query<CategoryGroup>("SELECT * FROM categorygroup").ToList();
        }

        public void SaveCategoryGroupChanges(CategoryGroup categoryGroup)
        {
            _db.Save(categoryGroup);
        }
        #endregion CategoryGroups

        #region Person
        public void CreateNewPerson(Person person)
        {
            _db.Insert(person);
        }

        public void DeletePerson(int id)
        {
            _db.Delete<Person>("WHERE personId=@0", id);
        }

        public Person GetPersonById(int id)
        {
            return _db.SingleOrDefault<Person>("WHERE personId=@0", id);
        }

        private Person GetPersonByDomainUserName(string domainUserName)
        {
            return _db.SingleOrDefault<Person>("WHERE DomainUserName=@0", domainUserName);
        }

        public void SavePersonChanges(Person person)
        {
            _db.Save(person);
        }

        public int GetOrCreatePersonId(string domainUserName)
        {
            Person person = GetPersonByDomainUserName(domainUserName);
            if (person == null)
            {
                person = new Person();
                person.DomainUserName = domainUserName;
                person.FirstName = "";
                person.LastName = "";
                person.Email = "";
                person.DateCreated = DateTime.UtcNow;
                person.DateUpdated = DateTime.UtcNow;
                person.CreatedBy = "System";
                person.UpdatedBy = "System";
                _db.Insert(person);
            }
            return person.PersonId;
        }
        #endregion Person

        #region Project
        public void CreateNewProject(Project project)
        {
            _db.Insert(project);
        }

        public void DeleteProject(int id)
        {
            _db.Delete<Project>("WHERE projectId=@0", id);
        }

        public Project GetProjectById(int id)
        {
            return _db.SingleOrDefault<Project>("WHERE projectId=@0", id);
        }

        public IEnumerable<Project> GetAllProjects()
        {
            return _db.Query<Project>("SELECT * FROM project").ToList();
        }

        public void SaveProjectChanges(Project project)
        {
            _db.Save(project);
        }

        public int CreateOrUpdateProject(ProjectDTO projectDTO)
        {
            var project = _db.SingleOrDefault<Project>("WHERE SourceSystem=@0", projectDTO.SourceSystem);
            if (project == null)
            {
                project = new Project();
                project.DateCreated = DateTime.UtcNow;
                project.DateUpdated = DateTime.UtcNow;
                project.UpdatedBy = "System";
                project.CreatedBy = "System";
            }
            Mapper.Map(projectDTO, project);
            if (project.ProjectId == 0)
            {
                _db.Insert("Project", "ProjectId", project);
                return project.ProjectId;
            }
            else 
            {
                _db.Update(project);
                return project.ProjectId;
            } 
        }
        #endregion Project

        #region Task
        public void CreateNewTask(Task task)
        {
            _db.Save(task);
        }

        public void DeleteTask(int id)
        {
            _db.Delete<Task>("WHERE taskId=@0", id);
        }

        public Task GetTaskById(int id)
        {
            return _db.SingleOrDefault<Task>("WHERE taskId=@0", id);
        }

        public IEnumerable<Task> GetAllTasks()
        {
            return _db.Query<Task>("SELECT * FROM task").ToList();
        }

        public void SaveTaskChanges(Task task)
        {
            _db.Save(task);
        }

        public int CreateOrUpdateTask(TaskDTO taskDTO, int projectID)
        {
            var task = _db.SingleOrDefault<Task>("WHERE SourceSystem=@0", taskDTO.SourceSystem);
            if (task == null)
            {
                task = new Task();
                task.DateCreated = DateTime.UtcNow;
                task.DateUpdated = DateTime.UtcNow;
                task.UpdatedBy = "System";
                task.CreatedBy = "System";
            }
            Mapper.Map(taskDTO, task);
            task.ProjectId = projectID;
            if (task.TaskId == 0)
            {
                _db.Insert("Task", "TaskId", task);
                return task.TaskId;
            }
            else
            {
                _db.Update(task);
                return task.TaskId;
            }
        }
        #endregion Task

        #region WorkCategory
        public void CreateNewWorkCategory(WorkCategory workCategory)
        {
            _db.Save(workCategory);
        }

        public void DeleteWorkCategory(int id)
        {
            _db.Delete<WorkCategory>("WHERE workcategoryId=@0", id);
        }

        public WorkCategory GetWorkCategoryById(int id)
        {
            return _db.SingleOrDefault<WorkCategory>("WHERE workcategoryId=@0", id);
        }

        public IEnumerable<WorkCategory> GetAllWorkCategories()
        {
            return _db.Query<WorkCategory>("SELECT * FROM workcategory").ToList();
        }

        public void SaveWorkCategoryChanges(WorkCategory workCategory)
        {
            _db.Save(workCategory);
        }

        public void BulkCreateWorkCategories(int worklogID, IEnumerable<int> categoryIDs)
        {
            var sql = PetaPoco.Sql.Builder.Append(";EXEC SetWorklogCategories");
            sql.Append("@@categoryIds = @0", string.Join(",", categoryIDs));
            sql.Append(", @@worklogID = @0", worklogID);
            _db.Execute(sql);
        }

        #endregion WorkCategory

        #region WorkLog
        public int CreateNewWorkLog(WorkLog workLog)
        {
            workLog.CreatedBy = "System";
            workLog.UpdatedBy = "System";
            workLog.DateCreated = DateTime.UtcNow;
            workLog.DateUpdated = DateTime.UtcNow;
            workLog.Name = workLog.Name ?? "";
            workLog.Description = workLog.Description ?? "";
            _db.Insert(workLog);
            return workLog.WorkLogId;
        }

        public void DeleteWorkLog(int id)
        {
            _db.Delete<WorkLog>("WHERE worklogId=@0", id);
        }

        public WorkLog GetWorkLogById(int id)
        {
            return _db.SingleOrDefault<WorkLog>("WHERE worklogId=@0", id);
        }

        public IEnumerable<WorkLog> GetAllWorkLogs()
        {
            return _db.Query<WorkLog>("SELECT * FROM WorkLog").ToList();
        }

        public IEnumerable<WorkLogDTO> GetWorklogsForUserByDateRange(int personID, DateTime from, DateTime to)
        {
            var sql = PetaPoco.Sql.Builder.Append("select worklog.*, task.*")
                .Append("from worklog")
                .Append("inner join task on worklog.taskId = task.taskid")
                .Append("WHERE PersonId=@0 AND Workdate >= @1 AND Workdate <= @2", personID, from, to);
            var worklogs = _db.Fetch<WorkLog, Task, WorkLogDTO>((w, t) => 
                                                    {   var worklogDTO = Mapper.Map<WorkLogDTO>(w);
                                                        worklogDTO.Task = Mapper.Map<TaskDTO>(t);
                                                        return worklogDTO; 
                                                    }, sql);
            //TODO: PetaPoco doesn't support multi results sets yet. 
            //But passing in a potentially large number of worklogIds is suboptimal
            var sqlForCategoriesQuery = Sql.Builder.Append("; EXEC GetWorklogCategories")
                .Append("@@worklogIDs = @0", string.Join(",", worklogs.Select(w => w.WorkLogId).ToList()));
            var workCategories = _db.Fetch<WorkCategory>(sqlForCategoriesQuery);
            foreach(var worklog in worklogs)
            {
                worklog.CategoryIDs = workCategories
                    .Where(w => w.WorkLogId == worklog.WorkLogId)
                    .Select(w => w.CategoryId).ToList();
            }
            return worklogs;
        }

        public void SaveWorkLogChanges(WorkLog workLog)
        {
            workLog.DateUpdated = DateTime.UtcNow;
            workLog.UpdatedBy = "System";
            _db.Save(workLog);
        }
         
        #endregion WorkLog

        #region Reports
        public IEnumerable<WorkLogReportItem> ReportWorkLoggedByCategory(DateTime? from, DateTime? to, int personId, int projectId)
        {
            try
            {
                var sql = PetaPoco.Sql.Builder.Append(";EXEC ReportWorkLoggedByCategory");
                sql.Append("@@DateTimeFrom = @0", from);
                sql.Append(", @@DateTimeTo = @0", to);
                sql.Append(", @@PersonId = @0", personId);
                sql.Append(", @@ProjectId = @0", projectId);
                return _db.Query<WorkLogReportItem>(sql);
            }
            catch(Exception ex)
            {
                string s = ex.Message;
                return null;
            }
        }
        #endregion

        #endregion
    }
}
